go
CREATE DATABASE MyImage_eProjectSem3
GO
Use MyImage_eProjectSem3
GO
CREATE TABLE Administrator (
	UserName varchar(20) PRIMARY KEY,
	[Password] varchar(30)
)
GO
CREATE TABLE Customer 
(
	CustomerID int PRIMARY KEY IDENTITY(1,1),
	UserName varchar(15) UNIQUE,
	[Password] varchar(30),
	CustomerName ntext,
	DoB datetime,
	Gender varchar(10),
	Email varchar(50),
	PhoneNumber numeric,
	[Address] ntext
)
GO
CREATE TABLE [Image]
(
ImageID int identity(1,1) primary key,
UserName varchar(15) FOREIGN KEY REFERENCES Customer(UserName),
ImageUrl varchar(300)
)
GO
CREATE TABLE Price 
(
	RES varchar(15) PRIMARY KEY,
	[Money] numeric
)
GO
CREATE TABLE PaymentMethods 
(
	PaymentMethodID int PRIMARY KEY IDENTITY(1,1),
	PaymentMethod varchar(20)
)
GO
CREATE TABLE Orders 
(
	OrderID int PRIMARY KEY IDENTITY(1, 1),
	CustomerID int FOREIGN KEY REFERENCES Customer(CustomerID),
	PaymentMethodID int FOREIGN KEY REFERENCES PaymentMethods(PaymentMethodID),
	OrderTime datetime,
	ShippingAddress varchar(50),
	PaymentDetail varchar(50),
	TotalPrice numeric,
	OrderStatus varchar(10)
)
GO
CREATE TABLE OrderDetails 
(
	OrderDetailsID int PRIMARY KEY IDENTITY(1,1),
	OrderID int FOREIGN KEY REFERENCES Orders(OrderID),
	ImgUrl varchar(300),
	RES varchar(15) FOREIGN KEY REFERENCES Price(RES),
	Quantity int,
	TPrice numeric
)
--
go
insert into Administrator values('admin','admin')
go
insert into PaymentMethods(PaymentMethod) values('Online')
insert into PaymentMethods(PaymentMethod) values('Direct')
go
insert into Price values('800x600',100)
insert into Price values('1024x768',200)
insert into Price values('1400x900',300)
insert into Price values('1920x1080',400)
go
go
-- create procedure spGetAccount
create procedure spGetAccount
@userName varchar(15)
as
	begin
		select * from Customer Where UserName = @userName
	end
-- create procedure spGetImageUrl
go
create procedure spGetImageUrl
@ID int
as
	begin
		select ImageUrl from [Image] where ImageID = @ID
	end
	
-- create procedure spGetPricebyRes
go
create procedure spGetPricebyRes
@RES varchar(15)
as	
	begin
		select * from Price where RES = @RES
	end

-- create procedure spAddOrder- Customer ID & Order Status
go
create procedure spAddOrder
@CustomerID int,
@Status varchar(10)
as
	begin
		insert into Orders(CustomerID, OrderStatus) values(@CustomerID, @Status)
	end
-- create procedure spAddOrderDetails - OrderID - 
go
create procedure spAddOrderDetails
@OrderID int,
@ImgUrl varchar(300),
@RES varchar(15),
@Quantity int,
@TotalPrice numeric
as
	begin
		insert into OrderDetails(OrderID, ImgUrl, RES, Quantity, TPrice)
		values(@OrderID, @ImgUrl, @RES, @Quantity, @TotalPrice)
	end
-- create procedure spGetAllOrder by Customer ID
go
create procedure spGetAllOrderByCusID
@CusID int
as	
	begin
		select A.OrderID, A.CustomerID, B.OrderDetailsID, B.OrderID, B.ImgUrl, B.RES, B.Quantity 
		from Orders A, OrderDetails B
		where A.CustomerID = @CusID and
			  A.OrderID = B.OrderID and
			  A.OrderStatus = 'Carting'
			  
			  
	end
-- create procedure spGetOrderIDbyCusID Assending
go
create procedure spGetOrderIDbyCusIDAssending
@CusID int
as
	begin
		select * from Orders Where CustomerID = @CusID Order by OrderID desc
	end
-- create procedure spUpdateOrderDetails
go
create procedure spUpdateOrderDetails
@OrderID int,
@OderDetailsID int,
@Qty int,
@Price numeric
as	
	begin
		update OrderDetails SET Quantity = @Qty, TPrice = @Price where OrderID = @OrderID and OrderDetailsID = @OderDetailsID
	end
-- create procedure getOrderCartting
go
create procedure spGetOrderCartting
@CusID int
as	
	begin
		select A.OrderID, A.CustomerID, B.OrderDetailsID, B.OrderID, B.ImgUrl, B.RES, B.Quantity, B.TPrice
		from Orders A, OrderDetails B
		where A.CustomerID = @CusID and
			  A.OrderID = B.OrderID and
			  A.OrderStatus = 'Carting'	  
	end
	

-- create procedure getUpdateOrder
go
create procedure spGetUpdateOrderAddress
@OID int,
@cusID int,
@add varchar(50),
@totalPrice numeric,
@payID int,
@payDetails varchar(20),
@orStt varchar(10)
as
	begin
		update Orders 
		SET OrderTime = GETDATE(),
		 ShippingAddress = @add,
		  TotalPrice = @totalPrice,
		   PaymentMethodID = @payID,
		    PaymentDetail = @payDetails,
		     OrderStatus = @orStt
		where CustomerID = @cusID and
			  OrderStatus = 'Carting' and
			  OrderID = @OID
	end
-- create procedure spGetPayMentDetails
go
create procedure spGetPaymentDetails
@detail varchar(20)
as
	begin
		select PaymentMethodID from PaymentMethods Where PaymentMethod = @detail
	end
select * from Orders
-- create procedure spGetOrderPendingDeclined
go
create procedure spGetOrderPendingDeclined
@CusID int
as	
	begin
		select A.OrderID, B.OrderDetailsID, B.ImgUrl, B.RES, B.Quantity, B.TPrice,A.PaymentDetail, A.OrderStatus
		from Orders A, OrderDetails B
		where A.CustomerID = @CusID and
			  A.OrderID = B.OrderID and
			  A.OrderStatus = 'Pending' Or
			  A.OrderStatus = 'Declined'	  
	end
go
--procedure
create procedure spViewAlbum
@UserName varchar(15)
AS
	BEGIN
		select * from [Image] where	UserName = @UserName
	END

go

create procedure spInsertImage
@UserName varchar(15),
@ImageUrl varchar(300)
AS
	BEGIN
		Insert into [Image] (UserName, ImageUrl) values (@UserName,@ImageUrl)
	END
go


create procedure spGetAllRes_Price
as
	begin
		Select * from Price order by [Money]
	end
go
-- create procedure spAddNewAccount
create procedure spAddNewAccount
@UserName varchar(15),
@Password varchar(30),
@CustomerName ntext,
@DoB datetime,
@Gender varchar(10),
@Email varchar(50),
@PhoneNumber numeric,
@Address ntext
as
	begin
		insert into Customer(UserName,[Password],CustomerName,DoB,Gender,Email,PhoneNumber,[Address]) 
		values(@UserName,@Password,@CustomerName, @DoB,@Gender, @Email,@PhoneNumber,@Address)
	end	

go
-- procedure update
create procedure spUpdateCustomer
@UserName varchar(15),
@Password varchar(30)
AS
	Begin
		UPDATE Customer SET [Password]= @Password Where UserName = @UserName
	End
	
go
--Admin login
create procedure spAdminLog
@user varchar(20)
as
	begin
	select * from Administrator where UserName = @user
	end
go	
--GetAllOrder
create procedure spGetAllOrderByOrderStatus
as
	begin
		select A.OrderID, B.UserName, A.OrderTime, A.ShippingAddress, A.PaymentDetail, A.TotalPrice, A.OrderStatus from Orders A, Customer B where
		A.OrderStatus != 'Carting' and
		A.CustomerID = B.CustomerID
	end
	
go
create procedure spGetOrderByOrderStatus
@oStatus varchar(10)
as
	begin
		select A.OrderID, B.UserName, A.OrderTime, A.ShippingAddress, A.PaymentDetail, A.TotalPrice, A.OrderStatus from Orders A, Customer B where
		A.OrderStatus = @oStatus and
		A.CustomerID = B.CustomerID
	end

go
create procedure spGetOrderDetails
@oid int
as	
	begin
	select A.OrderID, B.UserName, A.OrderTime, A.ShippingAddress, A.PaymentDetail, A.TotalPrice, A.OrderStatus from Orders A, Customer B where
		A.OrderID = @oid and
		A.CustomerID = B.CustomerID
	end
	
go 

-- Ship and Decline
create procedure spUpdateOrder
@oid int,
@status varchar(10)
as
	begin
		update Orders set OrderStatus = @status where OrderID = @oid
	end
	
go
-- get Customer
create procedure spGetCustomerDetails
@name varchar(15)
as
	begin
	Select * from Customer where UserName = @name
	end
go

-- get Order Details
create procedure spGetOrderDetailsbyID
@oid int
as
	begin
	select * from OrderDetails where OrderID = @oid
	end
	
-- get All customer
go
create procedure spGetAllCustomer
as
	begin
	select * from Customer
	end
go

create procedure spGetAllOrderbyCustomer
@cusID int
as
	begin
	select * from Orders where CustomerID = @cusID and OrderStatus != 'Carting'
	end

go
create procedure spSearchCustomer
@cusName varchar(15)
as
	begin
		select * from Customer Where UserName LIKE '%'+@cusName+'%'
	end
	
-- procedure delete orderDetails
go
create procedure spDeleteOrderDetails
@OrderID int,
@OderDetailsID int
as	
	begin
		delete from OrderDetails where OrderID = @OrderID and OrderDetailsID = @OderDetailsID
	end
	
